﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Transactions;
using Oracle.DataAccess.Client;
using System.Collections.Generic;

namespace LFramework.Data
{
    /// <summary>
    /// Oracle
    /// </summary>
    public class OracleHelper : IDBHelper
    {
        #region 构造函数
        public OracleHelper(string connStr)
        {
            Connection = new OracleConnection(connStr);
        }

        public OracleHelper(string server, string uid, string pwd)
        {
            Connection = new OracleConnection("server=" + server + ";uid=" + uid + ";pwd=" + pwd);
        }

        public OracleHelper(string server, string database, string uid, string pwd)
        {
            Connection = new OracleConnection("server=" + server + ";database=" + database + ";uid=" + uid + ";pwd=" + pwd);
        }

        #endregion

        #region 字段

        /// <summary>
        /// 默认超时时间
        /// </summary>
        int DefaultTimeOut = 3600;

        /// <summary>
        /// 数据库连接
        /// </summary>
        public OracleConnection Connection;
        /// <summary>
        /// 数据库命令
        /// </summary>
        public OracleCommand Command;

        #endregion

        #region 方法

        /// <summary>
        /// 执行SQL返回首行首列
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public object ExecuteScalar(string SQL)
        {
            return this.ExecuteScalar(SQL, DefaultTimeOut);
        }


        /// <summary>
        /// 执行SQL返回首行首列
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public object ExecuteScalar(string SQL, int timeOut)
        {
            try
            {
                Command = new OracleCommand(SQL, Connection);
                Command.CommandTimeout = timeOut;
                OpenConnection();
                return Command.ExecuteScalar();
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { CloseConnection(); }
        }


        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL(string SQL)
        {
            return ExecuteSQL(SQL, DefaultTimeOut);
        }


        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL(string SQL, int timeOut)
        {
            try
            {
                Command = new OracleCommand(SQL, Connection);
                Command.CommandTimeout = timeOut;
                OpenConnection();
                return Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { CloseConnection(); }
        }


        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL_InTrans(string SQL)
        {
            return ExecuteSQL_InTrans(SQL, DefaultTimeOut);
        }


        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL_InTrans(string SQL, int timeOut)
        {
            int i = 0;

            using (TransactionScope ts = new TransactionScope())
            {
                i = ExecuteSQL(SQL, timeOut);

                ts.Complete();
            }

            return i;
        }


        /// <summary>
        /// 执行参数式 SQL 
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <param name="paras">参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL(string SQL, Array paras)
        {
            return ExecuteSQL(SQL, paras, DefaultTimeOut);
        }


        /// <summary>
        /// 执行参数式 SQL 
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <param name="paras">参数</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL(string SQL, Array paras, int timeOut)
        {
            try
            {
                Command = new OracleCommand(SQL, Connection);
                Command.Parameters.AddRange(paras);
                OpenConnection();
                return Command.ExecuteNonQuery();
            }
            catch (Exception ex)
            { throw ex; }
            finally
            {
                CloseConnection();
            }
        }



        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL_InTrans(string SQL, Array paras)
        {
            return ExecuteSQL_InTrans(SQL, paras, DefaultTimeOut);
        }

        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>影响行数</returns>
        public int ExecuteSQL_InTrans(string SQL, Array paras, int timeOut)
        {
            int i = 0;

            using (TransactionScope ts = new TransactionScope())
            {
                i = ExecuteSQL(SQL, paras);

                ts.Complete();
            }

            return i;
        }



        /// <summary>
        /// 根据sql读取DataTable数据集
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>DataTable数据集</returns>
        public DataTable QueryForDataTable(string SQL)
        {
            return QueryForDataTable(SQL, DefaultTimeOut);
        }


        /// <summary>
        /// 根据sql读取DataTable数据集
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>DataTable数据集</returns>
        public DataTable QueryForDataTable(string SQL, int timeOut)
        {
            try
            {
                Command = new OracleCommand(SQL, Connection);
                Command.CommandTimeout = timeOut;
                DataTable dt = new DataTable();
                OpenConnection();
                using (Command)
                {
                    dt.Load(Command.ExecuteReader());
                }
                return dt;
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { CloseConnection(); }
        }


        /// <summary>
        /// 查询记录数
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>记录数</returns>
        public int QueryForCount(string SQL)
        {
            return QueryForCount(SQL, DefaultTimeOut);
        }

        /// <summary>
        /// 查询记录数
        /// </summary>
        /// <param name="SQL">sql</param>
        /// <returns>记录数</returns>
        public int QueryForCount(string SQL, int timeOut)
        {
            try
            {
                Command = new OracleCommand(SQL, Connection);
                OpenConnection();
                return (int)Command.ExecuteScalar();
            }
            catch (Exception ex)
            { throw ex; }
            finally
            { CloseConnection(); }
        }


        /// <summary>
        /// 执行批量拷贝
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="dt"></param>
        public void ExecuteBulkCopy(string tableName, DataTable dt)
        {
            try
            {
                IList<string> _columns = GetTableColumns(tableName).Split(',');  //glf 20131101 获取目标表的列数，防止目标表中的列和插入dt里面的列不匹配

                OpenConnection();

                OracleBulkCopy sbc = new OracleBulkCopy(Connection, OracleBulkCopyOptions.UseInternalTransaction);

                for (int i = 0; i < dt.Columns.Count; i++)
                {
                    for (int j = 0; j < _columns.Count; j++)
                    {
                        if (_columns[j].ToUpper().Equals(dt.Columns[i].ColumnName.ToUpper()))
                        {
                            sbc.ColumnMappings.Add(_columns[j], _columns[j]); //添加源和目标表之间的列映射。
                        }
                    }
                }

                sbc.BulkCopyTimeout = DefaultTimeOut;
                sbc.BatchSize = 50000; //一次更新的行数
                sbc.DestinationTableName = tableName;
                sbc.WriteToServer(dt);
            }
            catch { throw; }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 获取表的列名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>列名集合，例如 [GoodsCode,GoodsName,SalePrice] </returns>
        public string GetTableColumns(string tableName)
        {
            DataTable dataTable = new DataTable();

            try
            {
                OpenConnection();
                string sql = "select * from " + tableName + " where 1=2"; ;
                Command = new OracleCommand(sql, Connection);
                dataTable.Load(Command.ExecuteReader());
                string str = "";
                foreach (DataColumn dc in dataTable.Columns)
                {
                    str += dc.ColumnName + ",";
                }
                return str.TrimEnd(',');
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary> 
        /// <returns>连接状态</returns>
        public ConnectionState OpenConnection()
        {
            try
            {
                if (Connection.State == ConnectionState.Closed)
                {
                    Connection.Open();
                }
                return Connection.State;
            }
            catch { throw; }
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns>连接状态</returns>
        public ConnectionState CloseConnection()
        {
            try
            {
                if (Connection.State == ConnectionState.Open)
                {
                    Connection.Close();
                }
                return Connection.State;
            }
            catch { throw; }
        }


        /// <summary>
        /// 测试数据库连接
        /// </summary>
        /// <returns>成功/失败</returns>
        public bool TestConnection()
        {
            try
            {
                Connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion
    }
}
